# db_kit.py
import json
from pathlib import Path
import sqlite3
from typing import Any, Dict, List, Optional, Tuple, Union

from config.logger_config import get_default_logger
from config.sys_config import get_settings

settings = get_settings()
logger = get_default_logger(__name__)

class SQLiteKit:
    """通用 SQLite 工具类，基于外部配置动态管理数据库结构和操作"""
    _DB_CONNECTION: Optional[sqlite3.Connection] = None
    _SCHEMA: Optional[Dict[str, Any]] = None

    @staticmethod
    def _load_schema() -> Optional[Dict[str, Any]]:
        """加载数据库表结构配置"""
        if SQLiteKit._SCHEMA is None:
            schema_path = Path(settings.DB_SCHEMA_PATH)
            if not schema_path.exists():
                raise FileNotFoundError(f"数据库结构配置文件未找到: {schema_path}")
            with open(schema_path, 'r', encoding='utf-8') as f:
                SQLiteKit._SCHEMA = json.load(f)
            logger.info(f"✅ 已加载数据库结构配置: {schema_path}")
        return SQLiteKit._SCHEMA
    
    @staticmethod
    def _get_connection() -> sqlite3.Connection:
        """获取或初始化数据库连接"""
        if SQLiteKit._DB_CONNECTION is None:
            schema = SQLiteKit._load_schema()
            if schema is None:
                raise RuntimeError("数据库结构配置未加载")
            
            db_path = Path(settings.SQLITE_DB_PATH)
            db_path.parent.mkdir(parents=True, exist_ok=True)

            SQLiteKit._DB_CONNECTION = sqlite3.connect(str(db_path), check_same_thread=False, timeout=10.0)
            SQLiteKit._DB_CONNECTION.execute("PRAGMA journal_mode = WAL;") # 启用 WAL 模式提高并发性能
            SQLiteKit._DB_CONNECTION.execute("PRAGMA synchronous = NORMAL;") # 平衡性能与安全性

            # 动态创建表
            for table_name, table_def in schema['tables'].items():
                columns_sql_parts = []
                primary_key_col = None
                unique_cols = []
                for col in table_def['columns']:
                    col_sql = f"{col['name']} {col['type']}"
                    if col.get('primary_key'):
                        col_sql += " PRIMARY KEY"
                        primary_key_col = col['name']
                    if col.get('unique'):
                        unique_cols.append(col['name'])
                    columns_sql_parts.append(col_sql)

                create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(columns_sql_parts)})"
                SQLiteKit._DB_CONNECTION.execute(create_table_sql)
                
                # 为 unique 字段创建唯一索引约束
                for u_col in unique_cols:
                     SQLiteKit._DB_CONNECTION.execute(f"CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_{table_name}_{u_col} ON {table_name}({u_col});")

                # 创建用户定义的其他索引
                for index_def in table_def.get('indexes', []):
                    cols = ', '.join(index_def['columns'])
                    index_name = index_def.get('name', f"idx_{table_name}_{'_'.join(index_def['columns'])}")
                    SQLiteKit._DB_CONNECTION.execute(f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name}({cols});")

            SQLiteKit._DB_CONNECTION.commit()
            logger.info(f"✅ SQLite 数据库已在以下位置初始化: {db_path}")
        return SQLiteKit._DB_CONNECTION

    @staticmethod
    def execute(sql: str, params: Optional[Union[Tuple, List[Tuple]]] = None) -> bool:
        """
        执行任意 SQL 语句（INSERT, UPDATE, DELETE 等）
        
        Args:
            sql: SQL 语句
            params: 参数元组或参数元组列表
            
        Returns:
            bool: 执行是否成功
        """
        try:
            conn = SQLiteKit._get_connection()
            cursor = conn.cursor()
            if params and isinstance(params, list):
                cursor.executemany(sql, params)
            else:
                cursor.execute(sql, params or ())
            conn.commit()
            return True
        except Exception as e:
            logger.error(f"❌ 执行数据库语句失败: {e}")
            return False

    @staticmethod
    def select_one(sql: str, params: Optional[Tuple] = None) -> Optional[Tuple]:
        """
        查询单行数据
        
        Args:
            sql: SELECT SQL 语句
            params: 查询参数元组
            
        Returns:
            Optional[Tuple]: 查询结果的一行，如果没有则返回 None
        """
        try:
            conn = SQLiteKit._get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, params or ())
            return cursor.fetchone()
        except Exception as e:
            logger.error(f"❌ 查询单行数据失败: {e}")
            return None

    @staticmethod
    def select_all(sql: str, params: Optional[Tuple] = None) -> List[Tuple]:
        """
        查询多行数据
        
        Args:
            sql: SELECT SQL 语句
            params: 查询参数元组
            
        Returns:
            List[Tuple]: 查询结果的所有行列表
        """
        try:
            conn = SQLiteKit._get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, params or ())
            return cursor.fetchall()
        except Exception as e:
            logger.error(f"❌ 查询多行数据失败: {e}")
            return []

    @staticmethod
    def upsert(table_name: str, data: Dict[str, Any], conflict_columns: List[str]) -> bool:
        """
        通用 Upsert 方法（基于 ON CONFLICT）
        
        Args:
            table_name: 表名
            data: 要插入或更新的数据字典 {列名: 值}
            conflict_columns: 用于检测冲突的列名列表
            
        Returns:
            bool: 操作是否成功
        """
        if not data:
            logger.warning("Upsert 操作的数据字典为空")
            return False
            
        columns = list(data.keys())
        placeholders = ', '.join(['?' for _ in columns])
        # 准备更新部分，排除冲突列本身
        updates = ', '.join([f"{col}=excluded.{col}" for col in columns if col not in conflict_columns])

        sql = f"""
            INSERT INTO {table_name} ({', '.join(columns)})
            VALUES ({placeholders})
        """
        if updates:
            conflict_target = ', '.join(conflict_columns)
            sql += f" ON CONFLICT({conflict_target}) DO UPDATE SET {updates};"
        else:
            # 如果没有需要更新的列，则在冲突时什么都不做
            sql += " ON CONFLICT DO NOTHING;"

        try:
            conn = SQLiteKit._get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, tuple(data[col] for col in columns))
            conn.commit()
            return True
        except Exception as e:
            logger.error(f"❌ Upsert 操作失败: {e}")
            return False

    @staticmethod
    def close():
        """关闭数据库连接"""
        if SQLiteKit._DB_CONNECTION:
            SQLiteKit._DB_CONNECTION.close()
            SQLiteKit._DB_CONNECTION = None
            logger.info("🔌 SQLite 数据库连接已关闭。")